import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
column_aim_1 = 'IW'
column_aim_2 = '14'
column_aim_3 = '24'
column_aim_4 = '34'
column_aim_5 = '54'
xlsx_name = 'D:/repos/sicost/clean3_2.xlsx'
data_1 = pd.read_excel(xlsx_name)
data_1.columns = data_1.columns.str.upper()
data_1['PROD_DATE'] = data_1['PROD_DATE'].astype(str)
xlsx_name = 'D:/repos/sicost/clean3_1.xlsx'
data_0 = pd.read_excel(xlsx_name)
data_0.columns = data_0.columns.str.upper()
data_0['PROD_DATE'] = data_0['PROD_DATE'].astype(str)
data_2 = data_0[data_0['PRET_S_AIM'] == int(column_aim_2)]
data_2 = data_2.reset_index(drop=True)
data_3 = data_0[data_0['PRET_S_AIM'] == int(column_aim_3)]
data_3 = data_3.reset_index(drop=True)
data_4 = data_0[data_0['PRET_S_AIM'] == int(column_aim_4)]
data_4 = data_4.reset_index(drop=True)
data_5 = data_0[data_0['PRET_S_AIM'] == int(column_aim_5)]
data_5 = data_5.reset_index(drop=True)
data_all = pd.concat([data_1, data_2])
data_all = pd.concat([data_all, data_3])
data_all = pd.concat([data_all, data_4])
data_all = pd.concat([data_all, data_5])

sql_condition1 = " AND left(ST_NO,2) = 'IW' "
sql_condition2 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 14"
sql_condition3 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 24"
sql_condition4 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 34"
sql_condition5 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 54"
def clean_data(sql_condition):
    start = datetime.datetime.now()
    delta_day2 = 10
    delta_day1 = delta_day2 + 365
    p_day_2 = (start - datetime.timedelta(days=delta_day2)).strftime("%Y%m%d")
    p_day_1 = (start - datetime.timedelta(days=delta_day1)).strftime("%Y%m%d")
    sql = " select *  " \
          " FROM BGTAMOMMSM.T_ODS_TMMSM0701  " \
          " WHERE TC_PROC_FlAG = 2 AND PROD_DATE>='%s' " \
          " AND PROD_DATE<'%s' " \
          " %s ORDER BY PROD_DATE " % (p_day_1, p_day_2, sql_condition)
    print(sql)
    df0 = pd.read_sql_query(sql, con=db_conn_mpp)
    col_list = []
    col_list.append('PROD_DATE')
    col_list.append('ST_NO')
    col_list.append('AIM_ST_S')
    col_list.append('ACT_ST_S')
    col_list.append('PRET_S_AIM')
    col_list.append('REVISE_PRET_S')
    col_list.append('IRON_WT_TOTAL')
    # col_list.append('IRON_WT_NET')
    col_list.append('STTEMP_AFIRON')
    col_list.append('RECV_SI')
    col_list.append('RECV_S')
    col_list.append('RECV_TI')
    # col_list.append('AFTTEMP_RON1')
    col_list.append('AFTEMP_AFIRON')
    col_list.append('CAO_LOSS_SUM')
    col_list.append('CAO_DH')
    col_list.append('DES_WHISK_DEPTH1')
    print(col_list)

    df0.columns = df0.columns.str.upper()
    df0['PROD_DATE'] = df0['PROD_DATE'].astype(str)
    df = df0[col_list]
    df3 = df
    df3['DELTA'] = abs(df3['AIM_ST_S'] - df3['ACT_ST_S'])
    #数据清洗1
    df3_clean1 = df3[(df3['DELTA'] <= 100)]
    df3_clean1.drop(['ACT_ST_S'], axis=1, inplace=True)
    df3_clean1.drop(['DELTA'], axis=1, inplace=True)
    #数据清洗2
    def clean_data(df, gamma):
        column_name_list = df.columns.tolist()
        column_name_list.remove('PROD_DATE')
        column_name_list.remove('ST_NO')
        column_name_list.remove('AIM_ST_S')
        column_name_num = len(column_name_list)
        clean_str_start = 'df_new = df['
        clean_str_end = ']'
        ldict1 = {}
        for i in range(0, column_name_num):
            # print(i)
            # print(column_name_list[i])
            column_name_tmp = column_name_list[i]
            exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
            clean_str3 = ldict1["clean_str3"]
            if i == 0:
                clean_str_start = clean_str_start + clean_str3
            else:
                clean_str_start = clean_str_start + ' & ' + clean_str3
        clean_str = clean_str_start + clean_str_end
        # print(clean_str)
        exec(clean_str, locals(), ldict1)
        df_new = ldict1["df_new"]
        df_new = df_new.reset_index(drop=True)
        return df_new
    gamma = 1.5
    df3_clean2 = clean_data(df3_clean1, gamma)
    df3_clean2['DH'] = df3_clean2['CAO_LOSS_SUM'] / df3_clean2['IRON_WT_TOTAL'] * 100
    df3_clean2['TEMP'] = df3_clean2['STTEMP_AFIRON'] - df3_clean2['AFTEMP_AFIRON']
    #数据清洗3
    df3_clean3 = df3_clean2[(df3_clean2['TEMP'] > 0)]
    df3_clean3 = df3_clean3.reset_index(drop=True)
    df3_clean3.drop(['STTEMP_AFIRON'], axis=1, inplace=True)
    df3_clean3.drop(['AFTEMP_AFIRON'], axis=1, inplace=True)
    return df3_clean3
def average(num1, num2):
    return (num1 + num2) / 2
def cal_dh_model(data,column_aim_str):
    df4 = data
    if column_aim_str == 'IW':
        column_aim_tmp = 'IW'
    else:
        mode_fret_s_aim = int(column_aim_str)
        column_aim_tmp = 'AIM_' + str(int(mode_fret_s_aim))
    recv_s_max_list1 = [120, 140, 160, 180, 200, 300, 400, 500]
    recv_s_max_list2 = [0, 200, 300, 400, 500]
    gamma = 1.5
    q1 = df4['RECV_S'].quantile(0.25)
    q3 = df4['RECV_S'].quantile(0.75)
    iqr_val = q3 - q1
    q1_2 = df4['DH'].quantile(0.25)
    q3_2 = df4['DH'].quantile(0.75)
    iqr_val_2 = q3_2 - q1_2
    q1_3 = df4['DES_WHISK_DEPTH1'].quantile(0.25)
    q3_3 = df4['DES_WHISK_DEPTH1'].quantile(0.75)
    iqr_val_3 = q3_3 - q1_3
    df5 = df4[(df4['RECV_S'] <= q3 + gamma * iqr_val) & (df4['RECV_S'] >= q1 - gamma * iqr_val)
            & (df4['DH'] <= q3_2 + gamma * iqr_val_2) & (df4['DH'] >= q1_2 - gamma * iqr_val_2)
            & (df4['DES_WHISK_DEPTH1'] <= q3_3 + gamma * iqr_val_3) & (df4['DES_WHISK_DEPTH1'] >= q1_3 - gamma * iqr_val_3)]
    df5 = df5.reset_index(drop=True)
    #单耗
    df_out1 = pd.DataFrame(
                    columns=['RECV_S_MAX', 'dh1', 'dh2'])
    dict = {}
    for i in range(0,len(recv_s_max_list1)):
        print(recv_s_max_list1[i])
        if i == 0:
            interval = recv_s_max_list1[i+1]-recv_s_max_list1[i]
        else:
            interval = recv_s_max_list1[i]-recv_s_max_list1[i-1]
        min_tmp = recv_s_max_list1[i] - interval / 10
        max_tmp = recv_s_max_list1[i] + interval / 10
        df5_copy = df5.copy()
        df5_copy['DELTA_RECV_S'] = abs(df5_copy['RECV_S'] - recv_s_max_list1[i])
        df5_copy_sorted = df5_copy.sort_values(by='DELTA_RECV_S')
        # , ascending = False
        df5_copy_sorted = df5_copy_sorted.reset_index(drop=True)
        df5_tmp2 = df5_copy_sorted.head(10)
        df5_tmp1 = df5_copy[(df5_copy['RECV_S'] <= max_tmp) & (df5_copy['RECV_S'] >= min_tmp)]
        df5_tmp3 = df5_copy[(df5_copy['RECV_S'] == recv_s_max_list1[i])]
        row_count1 = len(df5_tmp1)
        row_count3 = len(df5_tmp3)
        if row_count3 > 0:
            y1_pred = df5_tmp3['DH'].mean()
            print(y1_pred)
        else:
            y1_pred = 0
        dict['RECV_S_MAX'] = recv_s_max_list1[i]
        dict['dh1'] = y1_pred
        if row_count1 <= 5:
            df5_tmp = df5_tmp2
        else:
            df5_tmp = df5_tmp1
        model = LinearRegression()
        X = df5_tmp['RECV_S'].values.reshape(-1, 1)
        y = df5_tmp['DH'].values
        model.fit(X, y)
        list_tmp = []
        list_tmp.append(recv_s_max_list1[i])
        array_tmp = np.array(list_tmp).reshape(-1, 1)
        y_pred = model.predict(array_tmp)
        # print(y_pred)
        y1_pred = y_pred[0]
        print(y1_pred)
        dict['dh2'] = y1_pred
        new_row = pd.Series(dict)
        df_out1 = df_out1.append(new_row, ignore_index=True)
    df_out1_copy = df_out1.copy()
    df_out1_copy = df_out1_copy[df_out1_copy['dh1'] != 0]
    df_out1_copy = df_out1_copy.reset_index(drop=True)
    model = LinearRegression()
    X = df_out1_copy['RECV_S_MAX'].values.reshape(-1, 1)
    y = df_out1_copy['dh1'].values
    model.fit(X, y)
    for index, row in df_out1.iterrows():
        if row['dh1'] == 0:
            x_pred = row['RECV_S_MAX']
            list_tmp = []
            list_tmp.append(x_pred)
            array_tmp = np.array(list_tmp).reshape(-1, 1)
            y_pred = model.predict(array_tmp)
            # print(y_pred)
            y1_pred = y_pred[0]
            row['dh1'] = y1_pred
    print(df_out1)
    values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[0], 'dh1']
    dh1_min = float(values)
    values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[0], 'dh2']
    dh2_min = float(values)
    values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[-1], 'dh1']
    dh1_max = float(values)
    values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[-1], 'dh2']
    dh2_max = float(values)
    dh_min = min(dh1_min, dh2_min)
    dh_max = max(dh1_max, dh2_max)
    df_out1['dh3'] = df_out1['dh2']
    df_out1['dh4'] = df_out1['dh2']
    for index, row in df_out1.iterrows():
        if index == 0:
            row['dh3'] = dh_min
            row['dh4'] = dh_min
        elif index == len(recv_s_max_list1)-1:
            row['dh3'] = dh_max
            row['dh4'] = dh_max
        else:
            dh3_tmp = dh_min + (dh_max - dh_min)/(recv_s_max_list1[-1] - recv_s_max_list1[0]) * (row['RECV_S_MAX'] - recv_s_max_list1[0])
            row['dh3'] = dh3_tmp
    df_out1['dh4_next'] = df_out1['dh4'].shift(-1)
    df_out1.dh4_next.fillna(99999, inplace=True)
    df_out1['dh4_pred'] = df_out1['dh4'].shift(1)
    df_out1.dh4_pred.fillna(0, inplace=True)
    change_value = 0
    df_out1['dh_tmp'] = df_out1['dh4']
    for index, row in df_out1.iterrows():
        if index != 0 and index != len(recv_s_max_list1)-1:
            #检验
            print(row['dh4_pred'])
            print(change_value)
            if row['dh4'] > change_value and row['dh4'] < row['dh4_next']:
                change_value = row['dh4']
            else:
                print('需要改变')
                new_value1 = average(row['dh3'], row['dh4'])
                if new_value1 > change_value and new_value1 < row['dh4_next']:
                    change_value = new_value1
                else:
                    new_value2 = average(row['dh3'], new_value1)
                    if new_value2 > change_value and new_value2 < row['dh4_next']:
                        change_value = new_value2
                    else:
                        change_value = row['dh3']
        else:
            change_value = row['dh4']
        row['dh_tmp'] = change_value
    df_out1 = df_out1[['RECV_S_MAX', 'dh_tmp']]
    df_out1.rename(columns={'dh_tmp': column_aim_tmp}, inplace=True)
    return df_out1


data_out_1 = cal_dh_model(data_1, column_aim_1)
data_out_2 = cal_dh_model(data_2, column_aim_2)
data_out_3 = cal_dh_model(data_3, column_aim_3)
data_out_4 = cal_dh_model(data_4, column_aim_4)
data_out_5 = cal_dh_model(data_5, column_aim_5)
v = ['RECV_S_MAX']
data_out_0 = pd.merge(data_out_1, data_out_2, on=v, how='left')
data_out_0 = pd.merge(data_out_0, data_out_3, on=v, how='left')
data_out_0 = pd.merge(data_out_0, data_out_4, on=v, how='left')
data_out_0 = pd.merge(data_out_0, data_out_5, on=v, how='left')
print(data_out_0)




df6 = data_all

recv_s_max_list2 = [0, 200, 300, 400, 500]
gamma = 1.5
q1 = df6['RECV_S'].quantile(0.25)
q3 = df6['RECV_S'].quantile(0.75)
iqr_val = q3 - q1
q1_2 = df6['DH'].quantile(0.25)
q3_2 = df6['DH'].quantile(0.75)
iqr_val_2 = q3_2 - q1_2
q1_3 = df6['DES_WHISK_DEPTH1'].quantile(0.25)
q3_3 = df6['DES_WHISK_DEPTH1'].quantile(0.75)
iqr_val_3 = q3_3 - q1_3
df7 = df6[(df6['RECV_S'] <= q3 + gamma * iqr_val) & (df6['RECV_S'] >= q1 - gamma * iqr_val)
        & (df6['DH'] <= q3_2 + gamma * iqr_val_2) & (df6['DH'] >= q1_2 - gamma * iqr_val_2)
        & (df6['DES_WHISK_DEPTH1'] <= q3_3 + gamma * iqr_val_3) & (df6['DES_WHISK_DEPTH1'] >= q1_3 - gamma * iqr_val_3)]
df7 = df7.reset_index(drop=True)
#时间
df_out2 = pd.DataFrame(
                columns=['RECV_S_MAX', 't1', 't2'])
dict = {}
for i in range(0,len(recv_s_max_list2)):
    print(recv_s_max_list2[i])
    if i == 0:
        interval = recv_s_max_list2[i+1]-recv_s_max_list2[i]
    else:
        interval = recv_s_max_list2[i]-recv_s_max_list2[i-1]
    min_tmp = recv_s_max_list2[i] - interval / 10
    max_tmp = recv_s_max_list2[i] + interval / 10
    df7_copy = df7.copy()
    df7_copy['DELTA_RECV_S'] = abs(df7_copy['RECV_S'] - recv_s_max_list2[i])
    df7_copy_sorted = df7_copy.sort_values(by='DELTA_RECV_S')
    # , ascending = False
    df7_copy_sorted = df7_copy_sorted.reset_index(drop=True)
    df7_tmp2 = df7_copy_sorted.head(10)
    df7_tmp1 = df7_copy[(df7_copy['RECV_S'] <= max_tmp) & (df7_copy['RECV_S'] >= min_tmp)]
    df7_tmp3 = df7_copy[(df7_copy['RECV_S'] == recv_s_max_list2[i])]
    row_count1 = len(df7_tmp1)
    row_count3 = len(df7_tmp3)
    if row_count3 > 0:
        y1_pred = df7_tmp3['DES_WHISK_DEPTH1'].mean()
        print(y1_pred)
    else:
        y1_pred = 0
    dict['RECV_S_MAX'] = recv_s_max_list2[i]
    dict['t1'] = y1_pred
    if row_count1 <= 5:
        df7_tmp = df7_tmp2
    else:
        df7_tmp = df7_tmp1
    model = LinearRegression()
    X = df7_tmp['RECV_S'].values.reshape(-1, 1)
    y = df7_tmp['DES_WHISK_DEPTH1'].values
    model.fit(X, y)
    list_tmp = []
    list_tmp.append(recv_s_max_list2[i])
    array_tmp = np.array(list_tmp).reshape(-1, 1)
    y_pred = model.predict(array_tmp)
    # print(y_pred)
    y1_pred = y_pred[0]
    print(y1_pred)
    dict['t2'] = y1_pred
    new_row = pd.Series(dict)
    df_out2 = df_out2.append(new_row, ignore_index=True)
df_out2_copy = df_out2.copy()
df_out2_copy = df_out2_copy[df_out2_copy['t1'] != 0]
df_out2_copy = df_out2_copy.reset_index(drop=True)
model = LinearRegression()
X = df_out2_copy['RECV_S_MAX'].values.reshape(-1, 1)
y = df_out2_copy['t1'].values
model.fit(X, y)
for index, row in df_out2.iterrows():
    if row['t1'] == 0:
        x_pred = row['RECV_S_MAX']
        list_tmp = []
        list_tmp.append(x_pred)
        array_tmp = np.array(list_tmp).reshape(-1, 1)
        y_pred = model.predict(array_tmp)
        # print(y_pred)
        y1_pred = y_pred[0]
        row['t1'] = y1_pred
print(df_out2)
values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't1']
t1_min = float(values)
values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't2']
t2_min = float(values)
values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't1']
t1_max = float(values)
values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't2']
t2_max = float(values)
t_min = min(t1_min, t2_min)
t_max = max(t1_max, t2_max)
df_out2['t3'] = df_out2['t2']
df_out2['t4'] = df_out2['t2']
for index, row in df_out2.iterrows():
    if index == 0:
        row['t3'] = t_min
        row['t4'] = t_min
    elif index == len(recv_s_max_list2)-1:
        row['t3'] = t_max
        row['t4'] = t_max
    else:
        t3_tmp = t_min + (t_max - t_min)/(recv_s_max_list2[-1] - recv_s_max_list2[0]) * (row['RECV_S_MAX'] - recv_s_max_list2[0])
        row['t3'] = t3_tmp
df_out2['t4_next'] = df_out2['t4'].shift(-1)
df_out2.t4_next.fillna(99999, inplace=True)
df_out2['t4_pred'] = df_out2['t4'].shift(1)
df_out2.t4_pred.fillna(0, inplace=True)
change_value = 0
df_out2['t_tmp'] = df_out2['t4']
for index, row in df_out2.iterrows():
    if index != 0 and index != len(recv_s_max_list2)-1:
        #检验
        print(row['t4_pred'])
        print(change_value)
        if row['t4'] > change_value and row['t4'] < row['t4_next']:
            change_value = row['t4']
        else:
            print('需要改变')
            new_value1 = average(row['t3'], row['t4'])
            if new_value1 > change_value and new_value1 < row['t4_next']:
                change_value = new_value1
            else:
                new_value2 = average(row['t3'], new_value1)
                if new_value2 > change_value and new_value2 < row['t4_next']:
                    change_value = new_value2
                else:
                    change_value = row['t3']
    else:
        change_value = row['t4']
    row['t_tmp'] = change_value
df_out2 = df_out2[['RECV_S_MAX', 't_tmp']]
df_out2.rename(columns={'t_tmp': 'WHISK_TIME'}, inplace=True)
print(df_out2)
print('finish')


